package com.ideal.manage.esms.repository.diagnostic;

import com.ideal.manage.esms.bean.diagnostic.DiagnoseResultInfo;
import com.ideal.manage.esms.repository.framework.BaseRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;

/**
 * Created By Dai On 2018/5/29
 * Remark: 诊断分析数据结果表 Repository
 */
public interface DiagnoseResultInfoRepository extends BaseRepository<DiagnoseResultInfo, String> {

    /**
     * 找到最近的最大的时间
     * @param id
     * @param name
     * @return
     */
    @Query("select max(d.createTime) from DiagnoseResultInfo d where d.setVal=?2 and d.equipment.id=?1 and d.isValid=1")
    Date findMaxTimeByIdAndName(String id, String name);

    /**
     * 找到对应时间对应的值
     * @param id
     * @param name
     * @param date
     * @return
     */
    @Query("select d.resultVal from DiagnoseResultInfo d where d.setVal=?2 and d.equipment.id=?1 and d.isValid=1 and d.createTime=?3")
    String findNowValueByIdAndNameAndTime(String id, String name, Date date);

    /**
     * 找到最近的第二最大的时间
     * @param id
     * @param name
     * @return
     */
    @Query("select max(d.createTime) from DiagnoseResultInfo d where d.setVal=?2 and d.equipment.id=?1 and d.isValid=1 and d.createTime<?3")
    Date findNextTimeByIdAndName(String id, String name, Date date);


//    //TODO
//    @Query("select max(d.resultVal) from DiagnoseResultInfo d where d.setVal=?2 and d.equipment.id=?1 and d.isValid=1 and d.createTime=?3")
//    String findNowValueByIdAndNameAndTime(String name, String time);

    @Query("select d from DiagnoseResultInfo d where d.isValid=1")
    List<DiagnoseResultInfo> findDiagnoses();

//    @Query("select d from DiagnoseResultInfo d where d.isValid=1 and d.setVal=?1 and d.createTime> ?2")
//    List<DiagnoseResultInfo> findDataByTime(String name,Date startDate);

    @Query("select d from DiagnoseResultInfo d where d.isValid=1 and d.equipment.id=?1 and d.createTime = ?2 ")
    List<DiagnoseResultInfo> findLastedData(String id,Date date);

    @Query("select max(d.createTime) from DiagnoseResultInfo d where d.equipment.id=?1 and d.isValid=1")
    Date findLastedDate(String id);

    @Query("select  d  from DiagnoseResultInfo d where d.isValid=1 and d.setVal=?1 order by d.createTime desc ")
    List<DiagnoseResultInfo> findNewData(String falg);


    @Query("select d from DiagnoseResultInfo d where d.isValid=1 and d.equipment.id=?1 and d.createTime>?2 and" +
            " d.createTime < ?3 and d.setVal=?4 order by d.createTime asc ")
    List<DiagnoseResultInfo> findBySoc(String id, Date startTime, Date endTime, String soc);

    @Query("select avg (d.resultVal)from DiagnoseResultInfo d where d.isValid=1 and d.setVal=?1 " +
            "and d.batch.time >?2 and d.batch.time <?3 and d.batch.ip=?4")
    String findByDcbrl(String dcbrl, long beginDayOfYear, long endDayOfYear, String ip);

    @Query(value = "select  max(d.resultVal) from DiagnoseResultInfo d where d.isValid=1" +
            "and d.batch.time >?1 and d.batch.time <?2 and d.setVal like ?3 and d.batch.ip=?4")
    String findDtZz(long startTime, long endTime, String s, String d);
}
